# 爬取豆瓣250数据 名字 评分 一句话评论 详情 链接
import time
import requests
from lxml import etree
import sqlite3

class Douban:
    def __init__(self):
        # 创建一个字典，用于最终的数据存储
        self.douban_dict = {}
        # 创建各列表，用于存储各个数据
        self.titles_list = []  # 标题
        self.scores_list = []  # 评分
        self.discuss_list = []  # 一句话评论
        self.Details_list = []  # 详情
        self.links_list = []     #链接
    def get_douban(self,page):
        for i in range(page): #page要循环的页数
            # douban250的url
            url = f"https://movie.douban.com/top250?start={i*25}&filter="

            # 请求头
            headers = {
                "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.159 Safari/537.36",
                "Host": "movie.douban.com",
                "Cookie": "bid=6SESd5XbpHc; _pk_ref.100001.4cf6=%5B%22%22%2C%22%22%2C1641121524%2C%22https%3A%2F%2Fwww.baidu.com%2Flink%3Furl%3D99M1ADnZ5VG0Ab2m0715iUFxZ1Vm_XCG7TpeCrKp9HQ9593UvKXqkgwLp1nGXjks%26wd%3D%26eqid%3Dc81bedf80001b5070000000261d186ec%22%5D; _pk_id.100001.4cf6=3b06f9d75cb2bfe9.1641121524.1.1641121524.1641121524.; _pk_ses.100001.4cf6=*; ap_v=0,6.0; __utma=30149280.645706298.1641121524.1641121524.1641121524.1; __utmb=30149280.0.10.1641121524; __utmc=30149280; __utmz=30149280.1641121524.1.1.utmcsr=baidu|utmccn=(organic)|utmcmd=organic; __utma=223695111.619501220.1641121524.1641121524.1641121524.1; __utmb=223695111.0.10.1641121524; __utmc=223695111; __utmz=223695111.1641121524.1.1.utmcsr=baidu|utmccn=(organic)|utmcmd=organic",
            }
            # 发起响应
            response = requests.get(url,headers=headers)
            # 获取response数据
            result = response.content.decode()
            # print(result)
            print(f"*****这是第{i+1}页数据******")

            # 将获取的数据转成html数据，用于xpath数据提取
            html = etree.HTML(result)
            time.sleep(2)

            # 标题
            titles = html.xpath('//*[@id="content"]/div/div[1]/ol/li[*]/div/div[2]/div[1]/a/span[1]/text()')
            # 将每次获取的标题数据存入列表中
            for i in titles:
                self.titles_list.append(i)
            print(f"标题列表：{self.titles_list}")
            time.sleep(2)

            # 评分
            scores = html.xpath('//*[@id="content"]/div/div[1]/ol/li[*]/div/div[2]/div[2]/div/span[2]/text()')
            # 将每次获取的评分数据存入列表中
            for i in scores:
                self.scores_list.append(i)
            print(f"评分列表：{self.scores_list}")
            time.sleep(2)

            # 一句话评论
            discuss = html.xpath('//*[@id="content"]/div/div[1]/ol/li[*]/div/div[2]/div[2]/p[2]/span/text()')
            # 将每次获取的一句话评论数据存入列表中
            for i in discuss:
                self.discuss_list.append(i)
            print(f"一句话评论列表：{self.discuss_list}")
            time.sleep(2)

            # 详情
            Details = html.xpath('//*[@id="content"]/div/div[1]/ol/li[*]/div/div[2]/div[2]/p[1]/text()')
            # 将每次获取的详情数据存入列表中
            for i in Details:
                self.Details_list.append(i)
            print(f"详情列表：{self.Details_list}")
            time.sleep(2)

            # 链接
            links = html.xpath('//*[@id="content"]/div/div[1]/ol/li[*]/div/div[2]/div[1]/a/@href')
            # 将每次获取的链接数据存入列表中
            for i in links:
                self.links_list.append(i)
            print(f"链接列表:{self.links_list}")
            time.sleep(2)


        # 将获取到的列表数据存入到最开始创建的字典中
        self.douban_dict['titles'] = self.titles_list
        self.douban_dict['scores'] = self.scores_list
        self.douban_dict['discuss'] = self.discuss_list
        self.douban_dict['Details'] = self.Details_list
        self.douban_dict['links'] = self.links_list

        print(f"{'*'*90}\n结果：\n{self.douban_dict}")
        return self.douban_dict

    # 将数据存入sqline3数据库中

    def save_sqline3(self):
        # 获取到爬取的数据
        douban = Douban()
        page = 10    #页数
        douban_dict = douban.get_douban(page)

        # 提取数据
        titles_list_sql = douban_dict['titles'] #电影名字
        scores_list_sql = douban_dict['scores'] #电影评分
        discuss_list_sql = douban_dict['discuss'] #一句话描述
        Details_list_sql = douban_dict['Details'] #详情
        links_list_sql = douban_dict['links'] #电影链接

        print("*****操作数据库*****")
        sqline = Sqline3()
        database_name = "douban" #数据库名

        # 创建表
        crate_table = """
                create table douban_movies(
                id INTEGER PRIMARY KEY autoincrement,
                movie_name text,
                scores real,
                discuss text,
                Details text,
                links text);
        """
        # 创建数据库并且创建表
        sqline.connect_sqline3(database_name,crate_table)
        # 插入数据
        insert_data = "insert into douban_movies values (%d,%s,%s,%s,%s,%s)"
        count = 0 #用于id
        for i in range(len(titles_list_sql)):
            insert_string_douban = insert_data % (count,repr(titles_list_sql[i]), repr(scores_list_sql[i]), repr(discuss_list_sql[i]), repr(Details_list_sql[i]),repr(links_list_sql[i]))
            print(f"sql语句{insert_string_douban}")
            # 连接数据库，插入数据
            sqline.connect_sqline3(database_name, insert_string_douban)
            time.sleep(1)
            count+=1
        print("最终数据插入成功")



# 封装操作Sqline3
class Sqline3:
    def connect_sqline3(self,path,sql):
        # 如果数据库存在及连接，如果数据库不存在则创建数据库并连接
        conn = sqlite3.connect(path+".db")
        print("数据库创建成功")
        cur = conn.cursor()  # 建立游标对象
        cur.execute(sql)  # 创建表
        conn.commit()  # 提交
        cur.close()  # 关闭游标对象
        conn.close()  # 关闭数据库连接
        print("sql语句执行完毕")



if __name__ == '__main__':
    douban = Douban()
    douban.save_sqline3()
